package eu.testing.system.dao.impl;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import org.springframework.jdbc.core.simple.ParameterizedRowMapper;
import org.springframework.jdbc.core.simple.SimpleJdbcDaoSupport;

import eu.testing.system.models.Assigned;
import eu.testing.system.models.Question;
import eu.testing.system.models.Test;
import eu.testing.system.controllers.SystemController;
import eu.testing.system.dao.AssignedDao;
import eu.testing.system.dao.QuestionDao;
import eu.testing.system.dao.TestDao;
//TODO implement checking - whether tables are created or no - normally
//TODO try to generalize jdbc source code
//TODO explore if hibernate is suitable

public class TestDaoImpl extends SimpleJdbcDaoSupport implements TestDao {
	private static final String SQL_DROP = "DROP TABLE IF EXISTS test";
	private static final String SQL_CREATE = "CREATE TABLE IF NOT EXISTS test("
			+ "TEST_ID int NOT NULL AUTO_INCREMENT,"
			+ "TITLE varchar(255) ,"
			+ "PRIMARY KEY (TEST_ID))";
	private static final String SQL_INSERT = "INSERT INTO test (TEST_ID, TITLE) VALUES (?, ?)";
	private static final String SQL_SELECT = "SELECT * FROM test WHERE TEST_ID = ?";
	private static final String SQL_SELECT_ALL = "SELECT * FROM test";
	private static final String SQL_DELETE = "DELETE FROM test WHERE TEST_ID = ?";
	private static final String SQL_UPDATE = "UPDATE test SET TITLE = ? WHERE TEST_ID = ?";
	SystemController controller = new SystemController();

	@Override
	public void dropTestTable() {
		getJdbcTemplate().update(SQL_DROP);
	}

	@Override
	public void createTestTable() {
		getJdbcTemplate().update(SQL_CREATE);

	}

	@Override
	public void insert(Test test) throws SQLException {
		createTestTable();

		getJdbcTemplate().update(SQL_INSERT, 0, test.getTitle());
		test.setId(getCurrentTestId(test));
		for (int i = 0; i < test.getQuestions().size(); i++) {
			Question question = test.getQuestions().get(i);
			question.setTestId(test.getId());
			question.setQuestionId(i);
			controller.questionDao.insertQuestion(question);
		}

	}

	@Override
	public Test getTest(int id) throws SQLException {
		createTestTable();
		Test test = getSimpleJdbcTemplate().queryForObject(SQL_SELECT,
				new TestParameterizedRowMapper(), id);

		List<Question> questions = controller.questionDao
				.getAllQuestionsById(test.getId());
		test.setQuestions(questions);

		return test;

	}

	@Override
	public void deleteTest(int id) throws SQLException {

		getJdbcTemplate().update(SQL_DELETE, id);
		controller.assignedDao.deleteAssigned(id);

		controller.questionDao.deleteQuestion(id);
		

	}

	@Override
	public void editTest(Test test) {
		getJdbcTemplate().update(SQL_UPDATE, test.getTitle(), test.getId());
		for (int i = 0; i < test.getQuestions().size(); i++) {
			Question question = test.getQuestions().get(i);
			question.setQuestionId(i);
			question.setTestId(test.getId());
			controller.questionDao.editQuestion(question);
		}

	}

	@Override
	public List<Test> getAllTests() throws SQLException {
		createTestTable();
		List<Test> tests = getSimpleJdbcTemplate().query(SQL_SELECT_ALL,
				new TestParameterizedRowMapper());

		return tests;
	}

	@Override
	public List<Test> getSpecificTests(String username) throws SQLException {
		List<Test> tests = new ArrayList<Test>();
		List<Assigned> as = controller.assignedDao.getAssigned(username);
		for (Assigned assign : as) {
			Test test = getTest(assign.getTestId());
			tests.add(test);

		}

		return tests;
	}

	private Integer getCurrentTestId(Test test) {
		String sql = "SELECT TEST_ID FROM test WHERE TITLE = '"
				+ test.getTitle() + "'";
		TestParameterizedRowMapper mapper = new TestParameterizedRowMapper();
		Integer testId = getJdbcTemplate().queryForInt(sql);

		return testId;

	}

	public class TestParameterizedRowMapper implements
			ParameterizedRowMapper<Test> {
		@Override
		public Test mapRow(ResultSet rs, int rowNum) throws SQLException {
			Test test = new Test();
			test.setId(rs.getInt("TEST_ID"));
			test.setTitle(rs.getString("TITLE"));
			return test;
		}

	}

}
